package com.pioneer.gui.invoice.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.Date;

import java.util.Vector;

import com.pioneer.gui.invoice.model.InvoiceModel;
import com.pioneer.gui.invoice.model.DropDownList;
import com.pioneer.gui.invoice.model.InvoiceTableModel;
import com.pioneer.gui.invoice.util.Constants;

import com.pioneer.beans.invoice.InvoiceHeaderModel;
import com.pioneer.beans.invoice.InvoiceDetailModel;

public class InvoiceDAO
implements Constants
{
	private Connection _conn;
	
	public InvoiceDAO()
	{
		ConnectionServer connServer=ConnectionServer.getSingleton();
        try
        {
        	_conn=connServer.getConnection();
        }
        catch (SQLException ex)
        {
        	System.out.println(ex.toString());
        }
	}
	
	public DropDownList getNewInvoiceNo()
	{
		String newNum="Unknown";
		Statement stat=null;
		ResultSet rs=null;
        try
        {        		
	       	stat=_conn.createStatement();        
	       	String selectString="SELECT MAX(d01_invoice_id) maxnum FROM d01_invoice_header";
            //System.out.println(deleteString);
            rs=stat.executeQuery(selectString);
            if (rs.next())
        	{
        		newNum=rs.getString("maxnum");
        	}        	
        }
        catch (SQLException e)
        {
	    	System.out.println(e.toString());
        }
        finally
       	{
	       	try
       		{
       			if (rs!=null) rs.close();
       			if (stat!=null) stat.close();
       		}
       		catch (SQLException e)
       		{
	       		System.out.println(e.toString());
       		}
       	}
       	DropDownList newDropDown=new DropDownList();
       	newDropDown.addItem(newNum);
       	return newDropDown;
	}
	
	public void saveDetails(String invoiceId,Vector tableData)
	{
		//System.out.println("Saving "+rowData[0]+" "+rowData[1]+" "+rowData[2]+" "+rowData[3]+" "+rowData[4]+" "+rowData[5]+" for "+invoiceId);
		Statement stat=null;
        try
        {        		
	       	stat=_conn.createStatement();        
	       	String deleteString="DELETE FROM d02_invoice_detail where d02_invoice_id='"+invoiceId+"'";
            System.out.println(deleteString);
            //stat.executeUpdate(deleteString);        
        	for (int rowCount=0; rowCount<tableData.size();rowCount++)
        	{
        		String[] rowData=(String[])tableData.get(rowCount);
        		String insertString="INSERT INTO d02_invoice_detail (d02_invoice_id,"+
        		"d02_item_no,d02_item_type,d02_desc,d02_qty,d02_qty_type,d02_price) values ('"+
        		invoiceId+"','"+rowData[0]+"','"+rowData[1]+"','"+SqlHelper.sqlSafe(rowData[2])+"','"+
        		rowData[3]+"','"+rowData[4]+"','"+rowData[5]+"')";
        		System.out.println(insertString);
        		//stat.executeUpdate(insertString);
        	}
        }
        catch (SQLException e)
        {
	    	System.out.println(e.toString());
        }
        finally
       	{
	       	try
       		{
       			if (stat!=null) stat.close();
       		}
       		catch (SQLException e)
       		{
	       		System.out.println(e.toString());
       		}
       	}	   
	}
	
	public String findDescByItemNo(String itemNo)
	{
		String resultString="Item not found";
		Statement stat=null;
        ResultSet rs=null;        
        try
        {        		
	       	stat=_conn.createStatement();        
            rs=stat.executeQuery("select b01_desc from b01_item where b01_item_no='"+itemNo+"'");
            if (rs.next())
            {
				resultString=rs.getString("b01_desc");
			}
        	rs.close();
			rs=null;
        }
        catch (SQLException e)
        {
	    	System.out.println(e.toString());
        }
        finally
       	{
	       	try
       		{
	       		if (rs!=null) rs.close();
       			if (stat!=null) stat.close();
       		}
       		catch (SQLException e)
       		{
	       		System.out.println(e.toString());
       		}
       	}       	
       	return resultString;
	}
	
	public String[] findAddressByContactId(int cusId, String contType, int contSeq)
	{
		String [] resultString= new String[6];
		Statement stat=null;
        ResultSet rs=null;        
        try
        {        		
	       	stat=_conn.createStatement();
	       	String selectString="SELECT c02_address,c02_city,c02_state,c02_zip,c02_phone,"+
            "c02_fax FROM c02_contact where c02_cus_id="+cusId+" and c02_cont_type='"+
            contType+"' and c02_cont_seq="+contSeq;
            rs=stat.executeQuery(selectString);
            if (rs.next())
            {
				resultString[0]=rs.getString("c02_address");
				resultString[1]=rs.getString("c02_city");
				resultString[2]=rs.getString("c02_state");
				resultString[3]=rs.getString("c02_zip");
				resultString[4]=rs.getString("c02_phone");
				resultString[5]=rs.getString("c02_fax");				
			}
        	rs.close();
			rs=null;
        }
        catch (SQLException e)
        {
	    	System.out.println(e.toString());
        }
        finally
       	{
	       	try
       		{
	       		if (rs!=null) rs.close();
       			if (stat!=null) stat.close();
       		}
       		catch (SQLException e)
       		{
	       		System.out.println(e.toString());
       		}
       	}
       	return resultString;
	}
	
	public void doSaveNew(InvoiceModel invoiceModel)
	{
				Statement stat=null;
        try
        {
	       	stat=_conn.createStatement();
	       	/*
	       	String insertString="insert into d01_invoice_header (d01_invoice_id,d01_cus_id,d01_date,"+
	       	"d01_entry_status,d01_sman_id,d01_subtotal,d01_discount,d01_total,d01_balance,d01_ship_via,"+
			"d01_terms,d01_ship_address,d01_ship_city,d01_ship_state,d01_ship_zip,d01_bill_address,"+
			"d01_bill_city,d01_bill_state,d01_bill_zip ) values ('"+invoiceModel.getInvoiceId()+"','"+
			invoiceModel.getCusId()+"','"+new Date(invoiceModel.getDate().getTime())+"','"+invoiceModel.getType()+"','"+
			ENTERED+"','"+invoiceModel.getSmanId()+"','"+invoiceModel.getSubTotal()+"','"+
			invoiceModel.getDiscount()+"','"+invoiceModel.getTotal()+"','"+invoiceModel.getBalance()+"','"+
			invoiceModel.getShipVia()+"','"+invoiceModel.getTerms()+"','"+invoiceModel.getShipInfo()[0]+"','"+
			invoiceModel.getShipInfo()[1]+"','"+invoiceModel.getShipInfo()[2]+"','"+invoiceModel.getShipInfo()[3]+"','"+
			invoiceModel.getShipInfo()[4]+"','"+invoiceModel.getShipInfo()[5]+"','"+
			invoiceModel.getBillInfo()[0]+"','"+invoiceModel.getBillInfo()[1]+"','"+
			invoiceModel.getBillInfo()[2]+"','"+invoiceModel.getBillInfo()[3]+"','"+
			invoiceModel.getBillInfo()[4]+"','"+invoiceModel.getBillInfo()[5]+"','"+"')";
	       	System.out.println(insertString);*/
            //stat.executeUpdate(insertString);
        }
        catch (SQLException e)
        {
	    	System.out.println(e.toString());
        }
        finally
       	{
	       	try
       		{
       			if (stat!=null) stat.close();
       		}
       		catch (SQLException e)
       		{
	       		System.out.println(e.toString());
       		}
       	}
	}
	
	public void doSaveExisting(InvoiceModel invoiceModel)
	{
		Statement stat=null;
        try
        {        		
	       	stat=_conn.createStatement();
	       	/*String updateString="update d01_invoice_header set d01_cus_id='"+invoiceModel.getCusId()+
	       	"',d01_type='"+invoiceModel.getType()+"',d01_entry_status='"+invoiceModel.getEntryStatus()+"',"+
	       	"d01_sman_id='"+invoiceModel.getSmanId()+"',d01_subtotal='"+invoiceModel.getSubTotal()+
	       	"',d01_discount='"+invoiceModel.getDiscount()+"',d01_total='"+invoiceModel.getTotal()+
	       	"',d01_balance='"+invoiceModel.getBalance()+"',d01_ship_via='"+invoiceModel.getShipVia()+
	       	"',d01_terms='"+invoiceModel.getTerms()+"',"+"d01_ship_address='"+invoiceModel.getShipInfo()[InvoiceModel.ADDRESS]+
	       	"',d01_ship_city='"+invoiceModel.getShipInfo()[InvoiceModel.CITY]+"',d01_ship_state='"+invoiceModel.getShipInfo()[InvoiceModel.STATE]+
	       	"',d01_ship_zip='"+invoiceModel.getShipInfo()[InvoiceModel.ZIP]+"',d01_ship_phone='"+invoiceModel.getShipInfo()[InvoiceModel.PHONE]+
	       	"',d01_ship_fax='"+invoiceModel.getShipInfo()[InvoiceModel.FAX]+"',d01_bill_address='"+invoiceModel.getBillInfo()[InvoiceModel.ADDRESS]+
	       	"',d01_bill_city='"+invoiceModel.getBillInfo()[InvoiceModel.CITY]+"',d01_bill_state='"+invoiceModel.getBillInfo()[InvoiceModel.STATE]+
	       	"',d01_bill_zip='"+invoiceModel.getBillInfo()[InvoiceModel.ZIP]+"',d01_bill_phone='"+invoiceModel.getBillInfo()[InvoiceModel.PHONE]+
	       	"',d01_bill_fax='"+invoiceModel.getBillInfo()[InvoiceModel.FAX]+"' where d01_invoice_id='"+
	       	invoiceModel.getInvoiceId()+"'";
	       	System.out.println(updateString);	       	*/
            //stat.executeUpdate(updateString);
        }
        catch (SQLException e)
        {
	    	System.out.println(e.toString());
        }
        finally
       	{
	       	try
       		{
       			if (stat!=null) stat.close();
       		}
       		catch (SQLException e)
       		{
	       		System.out.println(e.toString());
       		}
       	}	
	}
	
	public void refresh(InvoiceModel invoiceModel,String selectedInvoiceNo)
	{
		//InvoiceHeaderModel invoiceHeader=invoiceModel.getInvoiceHeader();
		InvoiceHeaderModel invoiceHeader=null;
		InvoiceTableModel invoiceDetails=invoiceModel.getInvoiceDetails();
		
		Statement stat=null;
        ResultSet rs=null;        
        try
        {        		
	       	stat=_conn.createStatement();        
            rs=stat.executeQuery("select d01_invoice_header.*, d03_desc, d04_desc, "+
            "c01_name,a01_name from d01_invoice_header,c01_customer, "+
            "a01_salesman, d03_entry_status_types, d04_invoice_types "+
            "where d01_invoice_id='"+selectedInvoiceNo+"' and "+
            "d01_cus_id=c01_cus_id and a01_sman_id=d01_sman_id and "+
            "d01_type=d04_type and d01_entry_status=d03_entry_status");
            if (rs.next())
            {
	        	invoiceHeader=new InvoiceHeaderModel(
	        			rs.getString("d01_invoice_id"),
        				rs.getInt("d01_cus_id"),
        				rs.getString("c01_name"),        			
        				rs.getDate("d01_date"),
        				rs.getString("d01_type"),
        				rs.getString("d04_desc"),
        				rs.getString("d01_entry_status"),
        				rs.getString("d03_desc"),
        				rs.getInt("d01_sman_id"),
        				rs.getString("a01_name"),
        				rs.getFloat("d01_subtotal"),
        				rs.getFloat("d01_discount"),
        				rs.getFloat("d01_total"),
        				rs.getFloat("d01_balance"),
        				rs.getString("d01_ship_via"),
        				rs.getString("d01_terms"),
        				rs.getString("d01_ship_address"),
        				rs.getString("d01_ship_city"),
        				rs.getString("d01_ship_state"),
        				rs.getString("d01_ship_zip"),
        				rs.getString("d01_ship_phone"),
        				rs.getString("d01_ship_fax"),        				
        				rs.getString("d01_bill_address"),
        				rs.getString("d01_bill_city"),
        				rs.getString("d01_bill_state"),
        				rs.getString("d01_bill_zip"),
        				rs.getString("d01_bill_phone"),
        				rs.getString("d01_bill_fax")        				
        				);
			}
			else
			{
				invoiceHeader=new InvoiceHeaderModel();
			}
        	rs.close();
        	// Now get the item list
        	rs=stat.executeQuery("select d02_invoice_detail.*,d05_qty_type_desc,d06_item_type_desc "+
        	"from d02_invoice_detail,d05_qty_types,d06_item_types where d02_invoice_id='"+
        	selectedInvoiceNo+"' and d02_qty_type=d05_qty_type and d02_item_type=d06_item_type");
        	Vector invoiceVector=new Vector();        	
        	while (rs.next())
        	{        			
	        	InvoiceDetailModel invoiceDetailModel=new InvoiceDetailModel(
        			rs.getString("d02_item_no"),
					rs.getString("d06_item_type_desc"),
        			rs.getString("d02_desc"),
        	    	rs.getInt("d02_qty"),
        	    	rs.getString("d05_qty_type_desc"),
        	    	rs.getFloat("d02_price")
        			);
        		invoiceVector.add(invoiceDetailModel);
        	}
        	invoiceDetails.setTableData(invoiceVector,selectedInvoiceNo);        	
        	invoiceModel.setInvoiceHeader(invoiceHeader);
        	invoiceModel.setInvoiceDetails(invoiceDetails);
        }
        catch (SQLException e)
        {
	    	System.out.println(e.toString());
        }
        finally
       	{
	       	try
       		{
	       		if (rs!=null) rs.close();
       			if (stat!=null) stat.close();
       		}
       		catch (SQLException e)
       		{
	       		System.out.println(e.toString());
       		}
       	}	   
	}    
}